package fashionshop.dal;

import java.sql.ResultSet;

import database.MySQLConnect;
import fashionshop.bll.StaffBLL;

public class StaffDAL {
	
	public StaffDAL(){
	}
	
	public static ResultSet LoadAll() throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sqlQuery = "SELECT * FROM staff";
		return conn.executeQuery(sqlQuery);
	}
	
	public static ResultSet LoadByID(int id) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "SELECT * FROM staff WHERE ID = '%d'";
		String sqlQuery = String.format(sql, id);
		return conn.executeQuery(sqlQuery);
	}
	
	public static ResultSet LoadByKeywork(String keywork) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "SELECT * FROM staff WHERE Name LIKE '%" + keywork + "%' or Phone LIKE '%" + keywork + "%' or IdentifyCard LIKE '%" + keywork + "%' or Username LIKE '%" + keywork + "%'";
		return conn.executeQuery(sql);
	}

	public static int Insert(StaffBLL staff) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "INSERT INTO staff (`Name`, `Phone`, `IdentifyCard`, `Username`, `Password`, `Active`, `Manager`) VALUES ('%s', '%s', '%s', '%s', '%d')";
		String sqlQuery = String.format(sql, staff.getName(), staff.getPhone(), staff.getIdentifyCard(), staff.getUsername(), staff.getPassword(), staff.getActive(), staff.getManager());
		return conn.executeUpdate(sqlQuery);
	}
	
	public static int Update(StaffBLL staff) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "UPDATE staff SET `Name`='%s', `Phone`='%s', `IdentifyCard`='%s', `Username`='%s', `Password`='%s', `Active`='%d', `Manager`='%d' WHERE `ID`='%d'";
		String sqlQuery = String.format(sql, staff.getName(), staff.getPhone(), staff.getIdentifyCard(), staff.getUsername(), staff.getPassword(), staff.getActive(), staff.getManager(), staff.getId());
		return conn.executeUpdate(sqlQuery);
	}
	
//	public static int Delete(StaffBLL staff) throws Exception{
//		MySQLConnect conn = new MySQLConnect();
//		String sql = "UPDATE staff SET `Active`='0' WHERE `ID`='%d'";
//		String sqlQuery = String.format(sql, staff.getId());
//		return conn.executeUpdate(sqlQuery);
//	}
//	
//	public static int Delete(int id) throws Exception{
//		MySQLConnect conn = new MySQLConnect();
//		String sql = "UPDATE staff SET `Active`='0' WHERE `ID`='%d'";
//		String sqlQuery = String.format(sql, id);
//		return conn.executeUpdate(sqlQuery);
//	}
}
